CentOS 7
Sponsored Link

SQL Server 2017 : Use from PHP
2017/10/28
 
This is an example to use SQL Server from PHP.
[1] This example is based on the environment that PHP 7.0 has been installed yet like here.
Furthermore, Install some required modules like follows.
# install from Remi

[root@dlp ~]#
yum --enablerepo=remi-safe -y groups install php70-php-pear php70-php-mbstring php70-php-pdo php70-php-devel

[root@dlp ~]#
yum -y groups install "Development Tools"

[root@dlp ~]#
pecl install sqlsrv pdo_sqlsrv

[root@dlp ~]#
echo 'extension=pdo_sqlsrv.so' > /etc/opt/remi/php70/php.d/30-pdo_sqlsrv.ini

[root@dlp ~]#
echo 'extension=sqlsrv.so' > /etc/opt/remi/php70/php.d/30-sqlsrv.ini

[2] Create a sample User and Database for Test.
[cent@dlp ~]$
sqlcmd -S localhost -U SA

Password:
# create login user
1> create login cent with PASSWORD= N'password';
2> go

# create [SampleDB]
1> create database SampleDB;
2> go

1> use SampleDB;
2> go
Changed database context to 'SampleDB'.

# create DB user
1> create user cent for login cent;
2> go

# asign DB owner role to [cent]
1> exec sp_addrolemember 'db_owner', 'cent';
2> go

# create [SampleTable]
1> create table SampleTable (
2> ID int identity(1,1) not null primary key, First_Name NVARCHAR(50), Last_Name NVARCHAR(50)
3> );
4> insert into SampleTable (
5> First_Name, Last_Name) values (N'CentOS', N'Linux'), (N'RedHat', N'Linux'), (N'Fedora', N'Linux'
6> );
7> go
[3] There are some basic usage to connect to SQL Server from PHP.
[cent@dlp ~]$
php -v

PHP 7.0.25 (cli) (built: Oct 24 2017 17:24:01) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies
[cent@dlp ~]$
mkdir MssqlTest

[cent@dlp ~]$
cd MssqlTest

[cent@dlp MssqlTest]$
vi use_mssql.php 
<?php
$serverName = "127.0.0.1";
$connectionOptions = array(
    "Database" => "SampleDB",
    "Uid" => "cent",
    "PWD" => "password"
);

$conn = sqlsrv_connect($serverName, $connectionOptions);

// Select from SampleTable
$tsql= "select * from SampleTable;";
$getResults= sqlsrv_query($conn, $tsql);
echo ("Reading data from SampleTable" . PHP_EOL);
if ($getResults == FALSE)
    die(FormatErrors(sqlsrv_errors()));
while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
    echo ($row['ID'] . " " . $row['First_Name'] . " " . $row['Last_Name'] . PHP_EOL);
}
sqlsrv_free_stmt($getResults);

// Insert from SampleTable
$tsql= "insert into SampleTable (First_Name, Last_Name) values (?,?);";
$params = array('Ubuntu','Linux');
$getResults= sqlsrv_query($conn, $tsql, $params);
echo ("\nInserting a new row into SampleTable" . PHP_EOL);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) inserted: " . PHP_EOL);
sqlsrv_free_stmt($getResults);

// Update from SampleTable
$userToUpdate = 'Redhat';
$tsql= "update SampleTable set Last_Name = ? where First_Name = ?";
$params = array('Maipo', $userToUpdate);
echo("\nUpdating Last_Name for user " . $userToUpdate . PHP_EOL);
$getResults= sqlsrv_query($conn, $tsql, $params);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) updated: " . PHP_EOL);
sqlsrv_free_stmt($getResults);

$tsql= "select * from SampleTable;";
$getResults= sqlsrv_query($conn, $tsql);
echo ("\nReading data from SampleTable" . PHP_EOL);
if ($getResults == FALSE)
    die(FormatErrors(sqlsrv_errors()));
while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
    echo ($row['ID'] . " " . $row['First_Name'] . " " . $row['Last_Name'] . PHP_EOL);
}
sqlsrv_free_stmt($getResults);

// Delete from SampleTable
$userToDelete = 'Ubuntu';
$tsql= "delete from SampleTable where First_Name = ?";
$params = array($userToDelete);
$getResults= sqlsrv_query($conn, $tsql, $params);
echo("\nDeleting user " . $userToDelete . PHP_EOL);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) deleted: " . PHP_EOL);
sqlsrv_free_stmt($getResults);

function FormatErrors( $errors )
{
    echo "Error information: ";

    foreach ( $errors as $error )
    {
        echo "SQLSTATE: ".$error['SQLSTATE']."";
        echo "Code: ".$error['code']."";
        echo "Message: ".$error['message']."";
    }
}
?>
[cent@dlp MssqlTest]$
php use_mssql.php 

Reading data from SampleTable
1 CentOS Linux
2 RedHat Linux
3 Fedora Linux

Inserting a new row into SampleTable
1 row(s) inserted:

Updating Last_Name for user Redhat
1 row(s) updated:

Reading data from SampleTable
1 CentOS Linux
2 RedHat Maipo
3 Fedora Linux
8 Ubuntu Linux

Deleting user Ubuntu
1 row(s) deleted:
 
Tweet